{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5fcd1456-0b7a-4814-8985-0c13f898949c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei'] # 用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus'] = False # 用来正常显示负号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5927e605-4868-4c59-bac3-82ea5738b9bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_excel(\"df.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2d510641-4530-47ea-885a-7597e36295a3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称  货品类型ID  \n",
       "0          103   金盛兰       1  \n",
       "1          124  德旺码头       0  \n",
       "2          121  江南码头       0  \n",
       "3          121  江南码头       0  \n",
       "4          104  武新一期       1  \n",
       "...        ...   ...     ...  \n",
       "49870      104  武新一期       1  \n",
       "49871      105  武新二期       1  \n",
       "49872      104  武新一期       1  \n",
       "49873      104  武新一期       1  \n",
       "49874      105  武新二期       1  \n",
       "\n",
       "[49875 rows x 9 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0ad3279d-e197-452e-9859-46623f6b6a6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "hplx=pd.read_excel('hplx.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a3a0a1bf-721d-49c2-9100-3ecc22790c1b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>dict_type</th>\n",
       "      <th>货品类型ID</th>\n",
       "      <th>货品类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>459</td>\n",
       "      <td>ERP_HPLX</td>\n",
       "      <td>0</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>460</td>\n",
       "      <td>ERP_HPLX</td>\n",
       "      <td>1</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID dict_type  货品类型ID 货品类型\n",
       "0  459  ERP_HPLX       0   水泥\n",
       "1  460  ERP_HPLX       1   矿粉"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hplx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "22407493-4b56-4bab-b4e0-1bc36bbc78c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(df, hplx[[\"货品类型ID\",\"货品类型\"]], on=\"货品类型ID\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "37e1ea5d-9b72-4c6f-a31a-e4ef737364cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型ID</th>\n",
       "      <th>货品类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>1</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>0</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>0</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>0</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>1</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>1</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>1</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称  货品类型ID 货品类型  \n",
       "0          103   金盛兰       1   矿粉  \n",
       "1          124  德旺码头       0   水泥  \n",
       "2          121  江南码头       0   水泥  \n",
       "3          121  江南码头       0   水泥  \n",
       "4          104  武新一期       1   矿粉  \n",
       "...        ...   ...     ...  ...  \n",
       "49870      104  武新一期       1   矿粉  \n",
       "49871      105  武新二期       1   矿粉  \n",
       "49872      104  武新一期       1   矿粉  \n",
       "49873      104  武新一期       1   矿粉  \n",
       "49874      105  武新二期       1   矿粉  \n",
       "\n",
       "[49875 rows x 10 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = merged_df.drop_duplicates(subset=['ID']) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "eca7d60e-683d-4d1e-b4d7-22b6379effd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.drop(columns=\"货品类型ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9e8774ae-89d6-4261-a1bd-7d129a114cac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称 货品类型  \n",
       "0          103   金盛兰   矿粉  \n",
       "1          124  德旺码头   水泥  \n",
       "2          121  江南码头   水泥  \n",
       "3          121  江南码头   水泥  \n",
       "4          104  武新一期   矿粉  \n",
       "...        ...   ...  ...  \n",
       "49870      104  武新一期   矿粉  \n",
       "49871      105  武新二期   矿粉  \n",
       "49872      104  武新一期   矿粉  \n",
       "49873      104  武新一期   矿粉  \n",
       "49874      105  武新二期   矿粉  \n",
       "\n",
       "[49875 rows x 9 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "39e79bea-fdbe-4a36-8dfc-cd9e26912e7a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>调度安排ID</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>站点信息ID</th>\n",
       "      <th>客户下单ID</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>到站吨位</th>\n",
       "      <th>单价</th>\n",
       "      <th>货款</th>\n",
       "      <th>车辆信息</th>\n",
       "      <th>收货时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>3624</td>\n",
       "      <td>218</td>\n",
       "      <td>319</td>\n",
       "      <td>1978</td>\n",
       "      <td>103</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>314</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>3637</td>\n",
       "      <td>107</td>\n",
       "      <td>93</td>\n",
       "      <td>1981</td>\n",
       "      <td>124</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>15490.40</td>\n",
       "      <td>241</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>3638</td>\n",
       "      <td>152</td>\n",
       "      <td>191</td>\n",
       "      <td>1986</td>\n",
       "      <td>121</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>43.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>14028.80</td>\n",
       "      <td>276</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>3639</td>\n",
       "      <td>152</td>\n",
       "      <td>191</td>\n",
       "      <td>1986</td>\n",
       "      <td>121</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>52.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>16704.00</td>\n",
       "      <td>257</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>3666</td>\n",
       "      <td>139</td>\n",
       "      <td>141</td>\n",
       "      <td>1998</td>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>32.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>9437.44</td>\n",
       "      <td>163</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>62734</td>\n",
       "      <td>461</td>\n",
       "      <td>744</td>\n",
       "      <td>32869</td>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>36.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1800.00</td>\n",
       "      <td>1345</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>62747</td>\n",
       "      <td>609</td>\n",
       "      <td>1145</td>\n",
       "      <td>32886</td>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>41.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>6379.80</td>\n",
       "      <td>106</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>62748</td>\n",
       "      <td>103</td>\n",
       "      <td>87</td>\n",
       "      <td>32893</td>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>34.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>5363.00</td>\n",
       "      <td>226</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>62745</td>\n",
       "      <td>201</td>\n",
       "      <td>892</td>\n",
       "      <td>32890</td>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>40.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>6908.80</td>\n",
       "      <td>125</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>62744</td>\n",
       "      <td>388</td>\n",
       "      <td>1278</td>\n",
       "      <td>32911</td>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>766</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID  调度安排ID  客户信息ID  站点信息ID  客户下单ID  发货地信息ID  货品类型  产品规格  产品品牌  \\\n",
       "0       2509    3624     218     319    1978      103     1     1     8   \n",
       "1       2510    3637     107      93    1981      124     0     0     0   \n",
       "2       2514    3638     152     191    1986      121     0     0     0   \n",
       "3       2515    3639     152     191    1986      121     0     0     0   \n",
       "4       2516    3666     139     141    1998      104     1     1     9   \n",
       "...      ...     ...     ...     ...     ...      ...   ...   ...   ...   \n",
       "49870  60712   62734     461     744   32869      104     1     1     9   \n",
       "49871  60713   62747     609    1145   32886      105     1     1     9   \n",
       "49872  60714   62748     103      87   32893      104     1     1     9   \n",
       "49873  60715   62745     201     892   32890      104     1     1     9   \n",
       "49874  60716   62744     388    1278   32911      105     1     1     9   \n",
       "\n",
       "        发货吨位  到站吨位     单价        货款  车辆信息                 收货时间  \n",
       "0      38.38   0.0  207.0   7944.66   314  2022-07-21 07:10:48  \n",
       "1      45.56   0.0  340.0  15490.40   241  2022-07-21 07:19:48  \n",
       "2      43.84   0.0  320.0  14028.80   276  2022-07-21 10:49:34  \n",
       "3      52.20   0.0  320.0  16704.00   257  2022-07-21 10:49:35  \n",
       "4      32.32   0.0  292.0   9437.44   163  2022-07-21 11:23:31  \n",
       "...      ...   ...    ...       ...   ...                  ...  \n",
       "49870  36.00   0.0   50.0   1800.00  1345  2024-06-24 13:28:49  \n",
       "49871  41.16   0.0  155.0   6379.80   106  2024-06-24 14:08:54  \n",
       "49872  34.60   0.0  155.0   5363.00   226  2024-06-24 14:09:20  \n",
       "49873  40.64   0.0  170.0   6908.80   125  2024-06-24 14:40:16  \n",
       "49874  35.68   0.0  150.0   5352.00   766  2024-06-24 15:07:41  \n",
       "\n",
       "[49875 rows x 15 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=pd.read_csv(\"ERP_FHJL.csv\")\n",
    "df1.columns=[\"ID\",\"调度安排ID\",\"客户信息ID\",\"站点信息ID\",\"客户下单ID\",\"发货地信息ID\",\"货品类型\",\"产品规格\",\"产品品牌\",\"发货吨位\",\"到站吨位\",\"单价\",\"货款\",\"车辆信息\",\"收货时间\"]\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6e4ef380-7b66-444e-8a28-8933f42110f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>站点ID</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>运货方式</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>发货地ID</th>\n",
       "      <th>到站时间</th>\n",
       "      <th>销售人员</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>79</td>\n",
       "      <td>37</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-13 15:59:10</td>\n",
       "      <td>xssb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>745</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-06-29 05:46:42</td>\n",
       "      <td>DS0403001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>865</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-01 04:30:19</td>\n",
       "      <td>DS0403001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>928</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>200.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-02 10:46:07</td>\n",
       "      <td>DS0403001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>958</td>\n",
       "      <td>141</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>150.00</td>\n",
       "      <td>1</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>121</td>\n",
       "      <td>2022-07-03 04:27:59</td>\n",
       "      <td>DS0403001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27948</th>\n",
       "      <td>32911</td>\n",
       "      <td>388</td>\n",
       "      <td>1278</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27949</th>\n",
       "      <td>32912</td>\n",
       "      <td>636</td>\n",
       "      <td>1211</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>35.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27950</th>\n",
       "      <td>32913</td>\n",
       "      <td>497</td>\n",
       "      <td>1274</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>42.36</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-05 08:05:00</td>\n",
       "      <td>chediao</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27951</th>\n",
       "      <td>32914</td>\n",
       "      <td>479</td>\n",
       "      <td>793</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>104</td>\n",
       "      <td>2024-06-24 08:05:00</td>\n",
       "      <td>DS0402003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27952</th>\n",
       "      <td>32915</td>\n",
       "      <td>546</td>\n",
       "      <td>979</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>30.00</td>\n",
       "      <td>0</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>105</td>\n",
       "      <td>2024-06-25 08:05:00</td>\n",
       "      <td>DS0403000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27953 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID  客户信息ID  站点ID  货品类型  产品规格  产品品牌    发货吨位  运货方式         业务类型描述  \\\n",
       "0         79      37    46     0     0     0  100.00     1  配送 (贸易+起驳+运输)   \n",
       "1        745     141   276     0     0     0  100.00     1  配送 (贸易+起驳+运输)   \n",
       "2        865     141   276     0     0     0  200.00     1  配送 (贸易+起驳+运输)   \n",
       "3        928     141   276     0     0     0  200.00     1  配送 (贸易+起驳+运输)   \n",
       "4        958     141   276     0     0     0  150.00     1  配送 (贸易+起驳+运输)   \n",
       "...      ...     ...   ...   ...   ...   ...     ...   ...            ...   \n",
       "27948  32911     388  1278     1     1     9   35.00     0        自提 (贸易)   \n",
       "27949  32912     636  1211     1     1     9   35.00     0        自提 (贸易)   \n",
       "27950  32913     497  1274     1     1     9   42.36     0        自提 (贸易)   \n",
       "27951  32914     479   793     1     1     9   30.00     0        自提 (贸易)   \n",
       "27952  32915     546   979     1     1     9   30.00     0     配送 (贸易+运输)   \n",
       "\n",
       "       发货地ID                 到站时间       销售人员  \n",
       "0        121  2022-06-13 15:59:10       xssb  \n",
       "1        121  2022-06-29 05:46:42  DS0403001  \n",
       "2        121  2022-07-01 04:30:19  DS0403001  \n",
       "3        121  2022-07-02 10:46:07  DS0403001  \n",
       "4        121  2022-07-03 04:27:59  DS0403001  \n",
       "...      ...                  ...        ...  \n",
       "27948    104  2024-06-24 08:05:00  DS0403003  \n",
       "27949    104  2024-06-24 08:05:00  DS0403003  \n",
       "27950    104  2024-06-05 08:05:00    chediao  \n",
       "27951    104  2024-06-24 08:05:00  DS0402003  \n",
       "27952    105  2024-06-25 08:05:00  DS0403000  \n",
       "\n",
       "[27953 rows x 12 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2=pd.read_csv(\"ERP_KHXD.csv\")\n",
    "df2.columns=[\"ID\",\"客户信息ID\",\"站点ID\",\"货品类型\",\"产品规格\",\"产品品牌\",\"发货吨位\",\"运货方式\",\"业务类型描述\",\"发货地ID\",\"到站时间\",\"销售人员\"]\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c0246716-7fcd-4824-8666-34e0ee6c491d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['产品规格ID']=df1['产品规格']\n",
    "df['产品品牌ID']=df1['产品品牌']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5dec9758-12c2-47e2-ac8a-c3c3668debc4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格ID</th>\n",
       "      <th>产品品牌ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称 货品类型  产品规格ID  产品品牌ID  \n",
       "0          103   金盛兰   矿粉       1       8  \n",
       "1          124  德旺码头   水泥       0       0  \n",
       "2          121  江南码头   水泥       0       0  \n",
       "3          121  江南码头   水泥       0       0  \n",
       "4          104  武新一期   矿粉       1       9  \n",
       "...        ...   ...  ...     ...     ...  \n",
       "49870      104  武新一期   矿粉       1       9  \n",
       "49871      105  武新二期   矿粉       1       9  \n",
       "49872      104  武新一期   矿粉       1       9  \n",
       "49873      104  武新一期   矿粉       1       9  \n",
       "49874      105  武新二期   矿粉       1       9  \n",
       "\n",
       "[49875 rows x 11 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "2c9141ef-ffff-42c1-896b-bc37daef6cdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "cpgg=pd.read_excel(\"cpgg.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "05c980ff-875c-42e1-9c7a-9e5cc795cb7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cpgg.columns=['id','dict_type','产品规格ID',\"产品规格\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "f083ac59-09d7-46c3-a769-8376cb6af372",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(df, cpgg[[\"产品规格ID\",\"产品规格\"]], on=\"产品规格ID\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "881ba17b-ca70-4fb8-af64-b6c083b1386d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格ID</th>\n",
       "      <th>产品品牌ID</th>\n",
       "      <th>产品规格</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称 货品类型  产品规格ID  产品品牌ID     产品规格  \n",
       "0          103   金盛兰   矿粉       1       8      S95  \n",
       "1          124  德旺码头   水泥       0       0  P.O42.5  \n",
       "2          121  江南码头   水泥       0       0  P.O42.5  \n",
       "3          121  江南码头   水泥       0       0  P.O42.5  \n",
       "4          104  武新一期   矿粉       1       9      S95  \n",
       "...        ...   ...  ...     ...     ...      ...  \n",
       "49870      104  武新一期   矿粉       1       9      S95  \n",
       "49871      105  武新二期   矿粉       1       9      S95  \n",
       "49872      104  武新一期   矿粉       1       9      S95  \n",
       "49873      104  武新一期   矿粉       1       9      S95  \n",
       "49874      105  武新二期   矿粉       1       9      S95  \n",
       "\n",
       "[49875 rows x 12 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = merged_df.drop_duplicates(subset=['ID']) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "8f4e0a57-46d3-4fdd-925f-89cac3ef00c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "cppp=pd.read_excel(\"cppp.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "4c7bc63b-74c1-4464-bf54-2ed50d44ccf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cpgg.columns=['id','dict_type','产品品牌ID',\"产品品牌\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "50ca8482-1b68-46cd-9c31-9b6fe75aa606",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(df, cpgg[[\"产品品牌ID\",\"产品品牌\"]], on=\"产品品牌ID\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "2224bb7a-35b7-4d86-83f3-a6a8163f1a9e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格ID</th>\n",
       "      <th>产品品牌ID</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称 货品类型  产品规格ID  产品品牌ID     产品规格     产品品牌  \n",
       "0          103   金盛兰   矿粉       1       8      S95      NaN  \n",
       "1          124  德旺码头   水泥       0       0  P.O42.5  P.O42.5  \n",
       "2          121  江南码头   水泥       0       0  P.O42.5  P.O42.5  \n",
       "3          121  江南码头   水泥       0       0  P.O42.5  P.O42.5  \n",
       "4          104  武新一期   矿粉       1       9      S95      NaN  \n",
       "...        ...   ...  ...     ...     ...      ...      ...  \n",
       "49870      104  武新一期   矿粉       1       9      S95      NaN  \n",
       "49871      105  武新二期   矿粉       1       9      S95      NaN  \n",
       "49872      104  武新一期   矿粉       1       9      S95      NaN  \n",
       "49873      104  武新一期   矿粉       1       9      S95      NaN  \n",
       "49874      105  武新二期   矿粉       1       9      S95      NaN  \n",
       "\n",
       "[49875 rows x 13 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = merged_df.drop_duplicates(subset=['ID']) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "ba83770e-2e5e-42df-9810-44698fb8853c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.drop(columns=\"产品品牌ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "c50d2433-60bc-4a67-967b-80f244f5b142",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['发货吨位']=df1[\"发货吨位\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "e88af85e-d8bd-49e5-9af2-4660f47be479",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['到站吨位']=df1[\"到站吨位\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "d538e19b-abfd-4836-bc96-264c3515bae1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['单价']=df1[\"单价\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "556bbcaa-ac3b-4675-afd9-6a80f731fdf1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['货款']=df1[\"货款\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "fe547044-f501-4f42-b1e1-d374a75dc693",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['车辆信息']=df1[\"车辆信息\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "317774aa-a5db-4007-9e91-1c253f3ae5e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.drop(columns=\"产品规格ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "d2e39efe-0c38-4365-ae53-ec1027281b05",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>到站吨位</th>\n",
       "      <th>单价</th>\n",
       "      <th>货款</th>\n",
       "      <th>车辆信息</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>45.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>15490.40</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>43.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>14028.80</td>\n",
       "      <td>276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>52.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>16704.00</td>\n",
       "      <td>257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>9437.44</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1800.00</td>\n",
       "      <td>1345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>6379.80</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>5363.00</td>\n",
       "      <td>226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>6908.80</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>766</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称 货品类型     产品规格     产品品牌   发货吨位  到站吨位     单价        货款  \\\n",
       "0          103   金盛兰   矿粉      S95      NaN  38.38   0.0  207.0   7944.66   \n",
       "1          124  德旺码头   水泥  P.O42.5  P.O42.5  45.56   0.0  340.0  15490.40   \n",
       "2          121  江南码头   水泥  P.O42.5  P.O42.5  43.84   0.0  320.0  14028.80   \n",
       "3          121  江南码头   水泥  P.O42.5  P.O42.5  52.20   0.0  320.0  16704.00   \n",
       "4          104  武新一期   矿粉      S95      NaN  32.32   0.0  292.0   9437.44   \n",
       "...        ...   ...  ...      ...      ...    ...   ...    ...       ...   \n",
       "49870      104  武新一期   矿粉      S95      NaN  36.00   0.0   50.0   1800.00   \n",
       "49871      105  武新二期   矿粉      S95      NaN  41.16   0.0  155.0   6379.80   \n",
       "49872      104  武新一期   矿粉      S95      NaN  34.60   0.0  155.0   5363.00   \n",
       "49873      104  武新一期   矿粉      S95      NaN  40.64   0.0  170.0   6908.80   \n",
       "49874      105  武新二期   矿粉      S95      NaN  35.68   0.0  150.0   5352.00   \n",
       "\n",
       "       车辆信息  \n",
       "0       314  \n",
       "1       241  \n",
       "2       276  \n",
       "3       257  \n",
       "4       163  \n",
       "...     ...  \n",
       "49870  1345  \n",
       "49871   106  \n",
       "49872   226  \n",
       "49873   125  \n",
       "49874   766  \n",
       "\n",
       "[49875 rows x 16 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "a2f71864-a6c6-437a-a988-23d54bd65970",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3=pd.read_csv(\"ERP_CLXX.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "f5d14b98-d478-47bb-a746-01162dd95829",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.columns=[\"车辆信息\",\"货运车辆车牌\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "3e83bde5-b1c1-4acf-9d24-bf285ec24722",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(df, df3[[\"车辆信息\",\"货运车辆车牌\"]], on=\"车辆信息\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "cbd20c66-ebfe-46bf-b9c9-9beff6aa77c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>客户信息ID</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>到站吨位</th>\n",
       "      <th>单价</th>\n",
       "      <th>货款</th>\n",
       "      <th>车辆信息</th>\n",
       "      <th>货运车辆车牌</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>218</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>314</td>\n",
       "      <td>鄂L1D516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>107</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>45.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>15490.40</td>\n",
       "      <td>241</td>\n",
       "      <td>鄂AEJ919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>43.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>14028.80</td>\n",
       "      <td>276</td>\n",
       "      <td>鄂ASE558</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>152</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>52.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>16704.00</td>\n",
       "      <td>257</td>\n",
       "      <td>鄂APP387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>139</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>9437.44</td>\n",
       "      <td>163</td>\n",
       "      <td>鄂APS131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>461</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1800.00</td>\n",
       "      <td>1345</td>\n",
       "      <td>鄂AET276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>609</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>6379.80</td>\n",
       "      <td>106</td>\n",
       "      <td>鄂ABQ252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>103</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>5363.00</td>\n",
       "      <td>226</td>\n",
       "      <td>鄂ADM620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>201</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>6908.80</td>\n",
       "      <td>125</td>\n",
       "      <td>鄂ABB206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>388</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>766</td>\n",
       "      <td>鄂ACK879</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间  客户信息ID       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48     218  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48     107  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35     152  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31     139  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...     ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49     461  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54     609    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20     103  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16     201  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41     388  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称 货品类型     产品规格     产品品牌   发货吨位  到站吨位     单价        货款  \\\n",
       "0          103   金盛兰   矿粉      S95      NaN  38.38   0.0  207.0   7944.66   \n",
       "1          124  德旺码头   水泥  P.O42.5  P.O42.5  45.56   0.0  340.0  15490.40   \n",
       "2          121  江南码头   水泥  P.O42.5  P.O42.5  43.84   0.0  320.0  14028.80   \n",
       "3          121  江南码头   水泥  P.O42.5  P.O42.5  52.20   0.0  320.0  16704.00   \n",
       "4          104  武新一期   矿粉      S95      NaN  32.32   0.0  292.0   9437.44   \n",
       "...        ...   ...  ...      ...      ...    ...   ...    ...       ...   \n",
       "49870      104  武新一期   矿粉      S95      NaN  36.00   0.0   50.0   1800.00   \n",
       "49871      105  武新二期   矿粉      S95      NaN  41.16   0.0  155.0   6379.80   \n",
       "49872      104  武新一期   矿粉      S95      NaN  34.60   0.0  155.0   5363.00   \n",
       "49873      104  武新一期   矿粉      S95      NaN  40.64   0.0  170.0   6908.80   \n",
       "49874      105  武新二期   矿粉      S95      NaN  35.68   0.0  150.0   5352.00   \n",
       "\n",
       "       车辆信息   货运车辆车牌  \n",
       "0       314  鄂L1D516  \n",
       "1       241  鄂AEJ919  \n",
       "2       276  鄂ASE558  \n",
       "3       257  鄂APP387  \n",
       "4       163  鄂APS131  \n",
       "...     ...      ...  \n",
       "49870  1345  鄂AET276  \n",
       "49871   106  鄂ABQ252  \n",
       "49872   226  鄂ADM620  \n",
       "49873   125  鄂ABB206  \n",
       "49874   766  鄂ACK879  \n",
       "\n",
       "[49875 rows x 17 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = merged_df.drop_duplicates(subset=['ID']) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "2ed7bc4f-727d-43e7-aa6e-d8c9b1917431",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.drop(columns=\"客户信息ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "63d71f3b-c26c-4fb3-9a0a-5c1ae96aa885",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地信息ID</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>到站吨位</th>\n",
       "      <th>单价</th>\n",
       "      <th>货款</th>\n",
       "      <th>车辆信息</th>\n",
       "      <th>货运车辆车牌</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>103</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>314</td>\n",
       "      <td>鄂L1D516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>124</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>45.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>15490.40</td>\n",
       "      <td>241</td>\n",
       "      <td>鄂AEJ919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>43.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>14028.80</td>\n",
       "      <td>276</td>\n",
       "      <td>鄂ASE558</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>121</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>52.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>16704.00</td>\n",
       "      <td>257</td>\n",
       "      <td>鄂APP387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>9437.44</td>\n",
       "      <td>163</td>\n",
       "      <td>鄂APS131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1800.00</td>\n",
       "      <td>1345</td>\n",
       "      <td>鄂AET276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>6379.80</td>\n",
       "      <td>106</td>\n",
       "      <td>鄂ABQ252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>5363.00</td>\n",
       "      <td>226</td>\n",
       "      <td>鄂ADM620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>104</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>6908.80</td>\n",
       "      <td>125</td>\n",
       "      <td>鄂ABB206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>105</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>766</td>\n",
       "      <td>鄂ACK879</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间       销售人员         业务类型描述      客户名称  \\\n",
       "0       2509  2022-07-21 07:10:48  DS0401002        自提 (贸易)  建工-水泥-恒泰   \n",
       "1       2510  2022-07-21 07:19:48  DS0404003        自提 (贸易)    湖北加瓦商贸   \n",
       "2       2514  2022-07-21 10:49:34  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "3       2515  2022-07-21 10:49:35  DS0402001  配送 (贸易+起驳+运输)     九欣通建材   \n",
       "4       2516  2022-07-21 11:23:31  DS0402003        自提 (贸易)      正洪岩土   \n",
       "...      ...                  ...        ...            ...       ...   \n",
       "49870  60712  2024-06-24 13:28:49  DS0401002       单运输 (运输)  建工-水泥-恒泰   \n",
       "49871  60713  2024-06-24 14:08:54    chediao     配送 (贸易+运输)    德旺码头仓库   \n",
       "49872  60714  2024-06-24 14:09:20  DS0403002        自提 (贸易)       NaN   \n",
       "49873  60715  2024-06-24 14:40:16  DS0402003        自提 (贸易)      正洪岩土   \n",
       "49874  60716  2024-06-24 15:07:41  DS0403003        自提 (贸易)      武汉中村   \n",
       "\n",
       "       发货地信息ID 发货地名称 货品类型     产品规格     产品品牌   发货吨位  到站吨位     单价        货款  \\\n",
       "0          103   金盛兰   矿粉      S95      NaN  38.38   0.0  207.0   7944.66   \n",
       "1          124  德旺码头   水泥  P.O42.5  P.O42.5  45.56   0.0  340.0  15490.40   \n",
       "2          121  江南码头   水泥  P.O42.5  P.O42.5  43.84   0.0  320.0  14028.80   \n",
       "3          121  江南码头   水泥  P.O42.5  P.O42.5  52.20   0.0  320.0  16704.00   \n",
       "4          104  武新一期   矿粉      S95      NaN  32.32   0.0  292.0   9437.44   \n",
       "...        ...   ...  ...      ...      ...    ...   ...    ...       ...   \n",
       "49870      104  武新一期   矿粉      S95      NaN  36.00   0.0   50.0   1800.00   \n",
       "49871      105  武新二期   矿粉      S95      NaN  41.16   0.0  155.0   6379.80   \n",
       "49872      104  武新一期   矿粉      S95      NaN  34.60   0.0  155.0   5363.00   \n",
       "49873      104  武新一期   矿粉      S95      NaN  40.64   0.0  170.0   6908.80   \n",
       "49874      105  武新二期   矿粉      S95      NaN  35.68   0.0  150.0   5352.00   \n",
       "\n",
       "       车辆信息   货运车辆车牌  \n",
       "0       314  鄂L1D516  \n",
       "1       241  鄂AEJ919  \n",
       "2       276  鄂ASE558  \n",
       "3       257  鄂APP387  \n",
       "4       163  鄂APS131  \n",
       "...     ...      ...  \n",
       "49870  1345  鄂AET276  \n",
       "49871   106  鄂ABQ252  \n",
       "49872   226  鄂ADM620  \n",
       "49873   125  鄂ABB206  \n",
       "49874   766  鄂ACK879  \n",
       "\n",
       "[49875 rows x 16 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "fd70b184-fba4-487c-ae4a-06b41c9b9b92",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"大表.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "bc3f45ad-f902-41b1-b938-8ad76e07aaeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_excel(\"大表.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "4e93e536-62de-4aa3-9c9c-104461575660",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>到站吨位</th>\n",
       "      <th>单价</th>\n",
       "      <th>货款</th>\n",
       "      <th>货运车辆车牌</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>鄂L1D516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>45.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>15490.40</td>\n",
       "      <td>鄂AEJ919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>43.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>14028.80</td>\n",
       "      <td>鄂ASE558</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>52.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>16704.00</td>\n",
       "      <td>鄂APP387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>9437.44</td>\n",
       "      <td>鄂APS131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49870</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1800.00</td>\n",
       "      <td>鄂AET276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49871</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>6379.80</td>\n",
       "      <td>鄂ABQ252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49872</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>5363.00</td>\n",
       "      <td>鄂ADM620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49873</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>6908.80</td>\n",
       "      <td>鄂ABB206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49874</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>鄂ACK879</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          ID                 收货时间       销售人员         业务类型描述      客户名称 发货地名称  \\\n",
       "0       2509  2022-07-21 07:10:48  DS0401002        自提 (贸易)  建工-水泥-恒泰   金盛兰   \n",
       "1       2510  2022-07-21 07:19:48  DS0404003        自提 (贸易)    湖北加瓦商贸  德旺码头   \n",
       "2       2514  2022-07-21 10:49:34  DS0402001  配送 (贸易+起驳+运输)     九欣通建材  江南码头   \n",
       "3       2515  2022-07-21 10:49:35  DS0402001  配送 (贸易+起驳+运输)     九欣通建材  江南码头   \n",
       "4       2516  2022-07-21 11:23:31  DS0402003        自提 (贸易)      正洪岩土  武新一期   \n",
       "...      ...                  ...        ...            ...       ...   ...   \n",
       "49870  60712  2024-06-24 13:28:49  DS0401002       单运输 (运输)  建工-水泥-恒泰  武新一期   \n",
       "49871  60713  2024-06-24 14:08:54    chediao     配送 (贸易+运输)    德旺码头仓库  武新二期   \n",
       "49872  60714  2024-06-24 14:09:20  DS0403002        自提 (贸易)       NaN  武新一期   \n",
       "49873  60715  2024-06-24 14:40:16  DS0402003        自提 (贸易)      正洪岩土  武新一期   \n",
       "49874  60716  2024-06-24 15:07:41  DS0403003        自提 (贸易)      武汉中村  武新二期   \n",
       "\n",
       "      货品类型     产品规格     产品品牌   发货吨位  到站吨位     单价        货款   货运车辆车牌  \n",
       "0       矿粉      S95      NaN  38.38   0.0  207.0   7944.66  鄂L1D516  \n",
       "1       水泥  P.O42.5  P.O42.5  45.56   0.0  340.0  15490.40  鄂AEJ919  \n",
       "2       水泥  P.O42.5  P.O42.5  43.84   0.0  320.0  14028.80  鄂ASE558  \n",
       "3       水泥  P.O42.5  P.O42.5  52.20   0.0  320.0  16704.00  鄂APP387  \n",
       "4       矿粉      S95      NaN  32.32   0.0  292.0   9437.44  鄂APS131  \n",
       "...    ...      ...      ...    ...   ...    ...       ...      ...  \n",
       "49870   矿粉      S95      NaN  36.00   0.0   50.0   1800.00  鄂AET276  \n",
       "49871   矿粉      S95      NaN  41.16   0.0  155.0   6379.80  鄂ABQ252  \n",
       "49872   矿粉      S95      NaN  34.60   0.0  155.0   5363.00  鄂ADM620  \n",
       "49873   矿粉      S95      NaN  40.64   0.0  170.0   6908.80  鄂ABB206  \n",
       "49874   矿粉      S95      NaN  35.68   0.0  150.0   5352.00  鄂ACK879  \n",
       "\n",
       "[49875 rows x 14 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "da1fb848-64df-4336-a5aa-b9c6da4e7b0f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>销售人员</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "      <td>测试一下</td>\n",
       "      <td>DS04001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>武汉昊恒(水泥)</td>\n",
       "      <td>DS0406000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>华安水泥(君善建)</td>\n",
       "      <td>DS0406000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>合力久盛(现金)</td>\n",
       "      <td>DS0404001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532</th>\n",
       "      <td>646</td>\n",
       "      <td>新中环</td>\n",
       "      <td>DS0403000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>533</th>\n",
       "      <td>647</td>\n",
       "      <td>湖北联发水泥厂</td>\n",
       "      <td>DS0501003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>648</td>\n",
       "      <td>武汉宏新途远商贸有限公司</td>\n",
       "      <td>DS0405003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>535</th>\n",
       "      <td>649</td>\n",
       "      <td>慧迪（授信）</td>\n",
       "      <td>DS0401002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536</th>\n",
       "      <td>650</td>\n",
       "      <td>华安水泥东湖分公司</td>\n",
       "      <td>DS0403003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>537 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id          客户名称       销售人员\n",
       "0     16          测试一下    DS04001\n",
       "1     18          武汉中村  DS0403003\n",
       "2     20      武汉昊恒(水泥)  DS0406000\n",
       "3     21     华安水泥(君善建)  DS0406000\n",
       "4     23      合力久盛(现金)  DS0404001\n",
       "..   ...           ...        ...\n",
       "532  646           新中环  DS0403000\n",
       "533  647       湖北联发水泥厂  DS0501003\n",
       "534  648  武汉宏新途远商贸有限公司  DS0405003\n",
       "535  649        慧迪（授信）  DS0401002\n",
       "536  650     华安水泥东湖分公司  DS0403003\n",
       "\n",
       "[537 rows x 3 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5=pd.read_csv(\"ERP_KHXX.csv\")\n",
    "df5.columns=[\"id\",'客户名称','销售人员']\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "78d0b822-429a-499e-aad4-c87d21161b0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(df, df5[[\"客户名称\",\"销售人员\"]], on=\"销售人员\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "7d63a285-548a-4082-87a3-a2de8bc2a207",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称_x</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>到站吨位</th>\n",
       "      <th>单价</th>\n",
       "      <th>货款</th>\n",
       "      <th>货运车辆车牌</th>\n",
       "      <th>客户名称_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>鄂L1D516</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>鄂L1D516</td>\n",
       "      <td>城开华埠(弘宏)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>鄂L1D516</td>\n",
       "      <td>湖北广建建筑</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>鄂L1D516</td>\n",
       "      <td>湖北鹏锦</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>鄂L1D516</td>\n",
       "      <td>李良忠(换货)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607413</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>鄂ACK879</td>\n",
       "      <td>世纪勇顺</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607414</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>鄂ACK879</td>\n",
       "      <td>枣阳翔吉顺</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607415</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>鄂ACK879</td>\n",
       "      <td>昆隆建材觅儿分公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607416</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>鄂ACK879</td>\n",
       "      <td>广福</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607417</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>鄂ACK879</td>\n",
       "      <td>华安水泥东湖分公司</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1607418 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID                 收货时间       销售人员   业务类型描述    客户名称_x 发货地名称 货品类型  \\\n",
       "0         2509  2022-07-21 07:10:48  DS0401002  自提 (贸易)  建工-水泥-恒泰   金盛兰   矿粉   \n",
       "1         2509  2022-07-21 07:10:48  DS0401002  自提 (贸易)  建工-水泥-恒泰   金盛兰   矿粉   \n",
       "2         2509  2022-07-21 07:10:48  DS0401002  自提 (贸易)  建工-水泥-恒泰   金盛兰   矿粉   \n",
       "3         2509  2022-07-21 07:10:48  DS0401002  自提 (贸易)  建工-水泥-恒泰   金盛兰   矿粉   \n",
       "4         2509  2022-07-21 07:10:48  DS0401002  自提 (贸易)  建工-水泥-恒泰   金盛兰   矿粉   \n",
       "...        ...                  ...        ...      ...       ...   ...  ...   \n",
       "1607413  60716  2024-06-24 15:07:41  DS0403003  自提 (贸易)      武汉中村  武新二期   矿粉   \n",
       "1607414  60716  2024-06-24 15:07:41  DS0403003  自提 (贸易)      武汉中村  武新二期   矿粉   \n",
       "1607415  60716  2024-06-24 15:07:41  DS0403003  自提 (贸易)      武汉中村  武新二期   矿粉   \n",
       "1607416  60716  2024-06-24 15:07:41  DS0403003  自提 (贸易)      武汉中村  武新二期   矿粉   \n",
       "1607417  60716  2024-06-24 15:07:41  DS0403003  自提 (贸易)      武汉中村  武新二期   矿粉   \n",
       "\n",
       "        产品规格 产品品牌   发货吨位  到站吨位     单价       货款   货运车辆车牌     客户名称_y  \n",
       "0        S95  NaN  38.38   0.0  207.0  7944.66  鄂L1D516   建工-水泥-恒泰  \n",
       "1        S95  NaN  38.38   0.0  207.0  7944.66  鄂L1D516   城开华埠(弘宏)  \n",
       "2        S95  NaN  38.38   0.0  207.0  7944.66  鄂L1D516     湖北广建建筑  \n",
       "3        S95  NaN  38.38   0.0  207.0  7944.66  鄂L1D516       湖北鹏锦  \n",
       "4        S95  NaN  38.38   0.0  207.0  7944.66  鄂L1D516    李良忠(换货)  \n",
       "...      ...  ...    ...   ...    ...      ...      ...        ...  \n",
       "1607413  S95  NaN  35.68   0.0  150.0  5352.00  鄂ACK879       世纪勇顺  \n",
       "1607414  S95  NaN  35.68   0.0  150.0  5352.00  鄂ACK879      枣阳翔吉顺  \n",
       "1607415  S95  NaN  35.68   0.0  150.0  5352.00  鄂ACK879  昆隆建材觅儿分公司  \n",
       "1607416  S95  NaN  35.68   0.0  150.0  5352.00  鄂ACK879         广福  \n",
       "1607417  S95  NaN  35.68   0.0  150.0  5352.00  鄂ACK879  华安水泥东湖分公司  \n",
       "\n",
       "[1607418 rows x 15 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "1d267d77-4291-47f9-8c4e-6c182269b09a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>收货时间</th>\n",
       "      <th>销售人员</th>\n",
       "      <th>业务类型描述</th>\n",
       "      <th>客户名称_x</th>\n",
       "      <th>发货地名称</th>\n",
       "      <th>货品类型</th>\n",
       "      <th>产品规格</th>\n",
       "      <th>产品品牌</th>\n",
       "      <th>发货吨位</th>\n",
       "      <th>到站吨位</th>\n",
       "      <th>单价</th>\n",
       "      <th>货款</th>\n",
       "      <th>货运车辆车牌</th>\n",
       "      <th>客户名称_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2509</td>\n",
       "      <td>2022-07-21 07:10:48</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>金盛兰</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.38</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>7944.66</td>\n",
       "      <td>鄂L1D516</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2510</td>\n",
       "      <td>2022-07-21 07:19:48</td>\n",
       "      <td>DS0404003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "      <td>德旺码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>45.56</td>\n",
       "      <td>0.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>15490.40</td>\n",
       "      <td>鄂AEJ919</td>\n",
       "      <td>湖北加瓦商贸</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>2514</td>\n",
       "      <td>2022-07-21 10:49:34</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>43.84</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>14028.80</td>\n",
       "      <td>鄂ASE558</td>\n",
       "      <td>九欣通建材</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>2515</td>\n",
       "      <td>2022-07-21 10:49:35</td>\n",
       "      <td>DS0402001</td>\n",
       "      <td>配送 (贸易+起驳+运输)</td>\n",
       "      <td>九欣通建材</td>\n",
       "      <td>江南码头</td>\n",
       "      <td>水泥</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>P.O42.5</td>\n",
       "      <td>52.20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>320.0</td>\n",
       "      <td>16704.00</td>\n",
       "      <td>鄂APP387</td>\n",
       "      <td>九欣通建材</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2516</td>\n",
       "      <td>2022-07-21 11:23:31</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>32.32</td>\n",
       "      <td>0.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>9437.44</td>\n",
       "      <td>鄂APS131</td>\n",
       "      <td>正洪岩土</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607220</th>\n",
       "      <td>60712</td>\n",
       "      <td>2024-06-24 13:28:49</td>\n",
       "      <td>DS0401002</td>\n",
       "      <td>单运输 (运输)</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1800.00</td>\n",
       "      <td>鄂AET276</td>\n",
       "      <td>建工-水泥-恒泰</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607253</th>\n",
       "      <td>60713</td>\n",
       "      <td>2024-06-24 14:08:54</td>\n",
       "      <td>chediao</td>\n",
       "      <td>配送 (贸易+运输)</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>6379.80</td>\n",
       "      <td>鄂ABQ252</td>\n",
       "      <td>德旺码头仓库</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607268</th>\n",
       "      <td>60714</td>\n",
       "      <td>2024-06-24 14:09:20</td>\n",
       "      <td>DS0403002</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>5363.00</td>\n",
       "      <td>鄂ADM620</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607269</th>\n",
       "      <td>60715</td>\n",
       "      <td>2024-06-24 14:40:16</td>\n",
       "      <td>DS0402003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>正洪岩土</td>\n",
       "      <td>武新一期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.64</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>6908.80</td>\n",
       "      <td>鄂ABB206</td>\n",
       "      <td>正洪岩土</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1607315</th>\n",
       "      <td>60716</td>\n",
       "      <td>2024-06-24 15:07:41</td>\n",
       "      <td>DS0403003</td>\n",
       "      <td>自提 (贸易)</td>\n",
       "      <td>武汉中村</td>\n",
       "      <td>武新二期</td>\n",
       "      <td>矿粉</td>\n",
       "      <td>S95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.68</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>5352.00</td>\n",
       "      <td>鄂ACK879</td>\n",
       "      <td>武汉中村</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49875 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID                 收货时间       销售人员         业务类型描述    客户名称_x 发货地名称  \\\n",
       "0         2509  2022-07-21 07:10:48  DS0401002        自提 (贸易)  建工-水泥-恒泰   金盛兰   \n",
       "33        2510  2022-07-21 07:19:48  DS0404003        自提 (贸易)    湖北加瓦商贸  德旺码头   \n",
       "86        2514  2022-07-21 10:49:34  DS0402001  配送 (贸易+起驳+运输)     九欣通建材  江南码头   \n",
       "97        2515  2022-07-21 10:49:35  DS0402001  配送 (贸易+起驳+运输)     九欣通建材  江南码头   \n",
       "108       2516  2022-07-21 11:23:31  DS0402003        自提 (贸易)      正洪岩土  武新一期   \n",
       "...        ...                  ...        ...            ...       ...   ...   \n",
       "1607220  60712  2024-06-24 13:28:49  DS0401002       单运输 (运输)  建工-水泥-恒泰  武新一期   \n",
       "1607253  60713  2024-06-24 14:08:54    chediao     配送 (贸易+运输)    德旺码头仓库  武新二期   \n",
       "1607268  60714  2024-06-24 14:09:20  DS0403002        自提 (贸易)       NaN  武新一期   \n",
       "1607269  60715  2024-06-24 14:40:16  DS0402003        自提 (贸易)      正洪岩土  武新一期   \n",
       "1607315  60716  2024-06-24 15:07:41  DS0403003        自提 (贸易)      武汉中村  武新二期   \n",
       "\n",
       "        货品类型     产品规格     产品品牌   发货吨位  到站吨位     单价        货款   货运车辆车牌  \\\n",
       "0         矿粉      S95      NaN  38.38   0.0  207.0   7944.66  鄂L1D516   \n",
       "33        水泥  P.O42.5  P.O42.5  45.56   0.0  340.0  15490.40  鄂AEJ919   \n",
       "86        水泥  P.O42.5  P.O42.5  43.84   0.0  320.0  14028.80  鄂ASE558   \n",
       "97        水泥  P.O42.5  P.O42.5  52.20   0.0  320.0  16704.00  鄂APP387   \n",
       "108       矿粉      S95      NaN  32.32   0.0  292.0   9437.44  鄂APS131   \n",
       "...      ...      ...      ...    ...   ...    ...       ...      ...   \n",
       "1607220   矿粉      S95      NaN  36.00   0.0   50.0   1800.00  鄂AET276   \n",
       "1607253   矿粉      S95      NaN  41.16   0.0  155.0   6379.80  鄂ABQ252   \n",
       "1607268   矿粉      S95      NaN  34.60   0.0  155.0   5363.00  鄂ADM620   \n",
       "1607269   矿粉      S95      NaN  40.64   0.0  170.0   6908.80  鄂ABB206   \n",
       "1607315   矿粉      S95      NaN  35.68   0.0  150.0   5352.00  鄂ACK879   \n",
       "\n",
       "           客户名称_y  \n",
       "0        建工-水泥-恒泰  \n",
       "33         湖北加瓦商贸  \n",
       "86          九欣通建材  \n",
       "97          九欣通建材  \n",
       "108          正洪岩土  \n",
       "...           ...  \n",
       "1607220  建工-水泥-恒泰  \n",
       "1607253    德旺码头仓库  \n",
       "1607268       NaN  \n",
       "1607269      正洪岩土  \n",
       "1607315      武汉中村  \n",
       "\n",
       "[49875 rows x 15 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = merged_df.drop_duplicates(subset=['ID']) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "0df38a87-302d-4a1b-bd08-6008baa86b7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"df.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2612d24-a40c-41cb-9fa4-dbb3248182e6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
